#!/usr/bin/env bash
source /etc/profile

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop3<<EOF
use result;
SELECT substr(minutes,1,13),stationID,
sum(case when is_oil=1 then 0 else numbera end)
,sum(numbera) from
(SELECT minutes,stationID,oilNumber numbera,1 is_oil from res_oil where
substr(minutes,1,10)='$dt'
union all
select minutes,stationID,notoilNumber numbera,0 is_oil
from res_notoil
where substr(minutes,1,10)='$dt'
) tmp
group by SUBSTR(minutes,1,13),stationID;

EOF